#!/usr/bin/env python
#-*- coding: UTF-8 -*- 
# edit legs.ant.king@gmail.com by 20111111
from MySQLdb import *
import time
import datetime
import xlwt
import sys
reload(sys)
sys.setdefaultencoding('utf8')

class bcolors:
    HEADER = '\033[95m'
    OKBLUE = '\033[94m'
    OKGREEN = '\033[92m'
    WARNING = '\033[93m'
    FAIL = '\033[91m'
    ENDC = '\033[0m'
    def disable(self):
        self.HEADER = ''
        self.OKBLUE = ''
        self.OKGREEN = ''
        self.WARNING = ''
        self.FAIL = ''
        self.ENDC = ''
def con() :
	con = None
	try :
		con = connect( host = '' , user = '' , passwd = '' , port = 3306 , db = '' )
	except Exception , e :
		print bcolors.OKBLUE + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + 'Can\'t Connect to Database Successful '+ str( e ) + bcolors.ENDC
	return con
daidx = u"""
select
  b.dates
, round( b.darow / 10000000 , 2 ) 'RowBillion'
, round( b.dalen / 1073741824 , 2 ) DG
, round( b.idlen / 1073741824 , 2 ) IG
, round( ( b.dalen + b.idlen ) / 1073741824 , 2 ) CapG
, round( ( b.darow  -a.darow ) / 10000 , 2 ) IncRow
, round( ( b.dalen - a.dalen ) / 1048576 , 2 ) IncDatM
, round( ( b.idlen - a.idlen ) / 1048576 , 2 ) IncIdxM
, round( ( b.inc - a.inc ) / 1048576 , 2 ) IncM
from
(
select 
	  sum(rows) darow
	, sum(datalen) dalen
	, sum(idx_len) idlen
	, ( sum(datalen) + sum(idx_len) ) inc
	, days dates
from
	status.status_database a
where address != '10.252.44.201'
group by days ) a inner join (
select 
	  sum(rows) darow
	, sum(datalen) dalen
	, sum(idx_len) idlen
	, ( sum(datalen) + sum(idx_len) )  inc
	, date_add( days , interval -1 day ) dates
from
	status.status_database a
where address != '10.252.44.201'
group by days ) b
on a.dates = b.dates
"""
def sql_mode( mode , tab , ins , btm , etm , fetlist ) :
	sql = "SELECT %s FROM %s WHERE instance_id = %s AND createtime BETWEEN '%s' and '%s' ORDER BY id " % ( fetlist , tab , ins , btm , etm )
	if mode == 'a' : 
		sql = sql + "ASC LIMIT 1"
	elif mode == 'd' :
		sql = sql + "DESC LIMIT 1"
	else :
		print bcolors.OKBLUE + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + 'Unkown Sort mode' + bcolors.ENDC
		sql = None
	return sql
def main() :
	debug = 0 # disable debug
	tb = 32
	dyn = 30
	db = con()
	cursor = db.cursor( cursorclass=cursors.DictCursor )
	cursor.execute( 'USE dbadmin' )
	cursor.execute( 'SELECT id , ip FROM instance WHERE status = 1' )
	srvs = cursor.fetchall()
	cursor.execute( 'USE status' )
	ret = []
	pubret = [ 'days' , 'server' , 'qps' , 'tps' , 'inr' , 'inw', 'log' , 'dbw' , 'dbw' , 'inwio' , 'inrio' , 'inio' , 'inrg' , 'inwg' , 'inwrg' , 'inrq' , 'inwq' , 'inreq' , 'keyrio' , 'keywio' , 'keyio' , 'wio' , 'rio' , 'io' , 'netr' , 'nets' , 'net' ]
	
	fetlist = 'com_delete , com_insert , com_update  , innodb_data_read , innodb_data_reads , innodb_data_writes , innodb_data_written , innodb_os_log_written , innodb_log_writes , innodb_dblwr_pages_written , innodb_dblwr_writes , uptime , bytes_received , bytes_sent , key_reads , key_writes , key_read_requests , questions'
	fetlis = [ 'com_delete' , 'com_insert' , 'com_update ' , 'innodb_data_read' , 'innodb_data_reads' , 'innodb_data_writes' , 'innodb_data_written' , 'innodb_os_log_written' , 'innodb_log_writes' , 'innodb_dblwr_pages_written' , 'innodb_dblwr_writes' , 'uptime' , 'bytes_received' , 'bytes_sent' , 'key_reads' , 'key_writes' , 'key_read_requests' , 'questions' ]
	days=dyn
	fi = 'stat.%s.xls' % str(datetime.datetime.now()).replace('-','')[:8]
	wbk = xlwt.Workbook()
	print bcolors.OKBLUE + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + ' Program started Write Data to Microsoft Excel %s' % fi + bcolors.ENDC
	f = wbk.add_sheet('status_detail')
	column=0
	rows = 0
	for i in pubret :
		f.write( 0 , column , i )
		column+=1
	for flloop in xrange( dyn ) :
		srv_ret = {}
		publisr = []
		dt = datetime.datetime.now()
		t1 = days - days * 2
		t2 = days - days * 2 + 1
		date1 = str( dt + datetime.timedelta( days = t1 ) ).replace('-','')[:8]
		date2 = str( dt + datetime.timedelta( days = t2 ) ).replace('-','')[:8]
		date = date1[4:]
		print bcolors.OKBLUE + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + ' Celler data %s' % ( date ) + bcolors.ENDC
		#print date
		for srv in srvs :
			table = 'status_detail_' + str( srv[ 'id' ] % tb )
			ins = srv[ 'id' ]
			try :
				cursor.execute( sql_mode( 'a' , table , ins , date1 , date2 ,fetlist ) )
				if cursor.rowcount < 1 :
					if debug == 1 :
						print bcolors.OKBLUE + "Can't Found Row continue to before days %s" % srv[ 'ip' ] + bcolors.ENDC
					continue
			except Exception , e :
				print bcolors.OKBLUE + str(e) + bcolors.ENDC
			mins = cursor.fetchone()
			try :
				cursor.execute( sql_mode( 'd' , table , ins , date1 , date2 , fetlist ) )
				if cursor.rowcount < 1 :
					if debug == 1 :
						print bcolors.OKBLUE + "Can't Found Row continue to before days %s" % srv[ 'ip' ] + bcolors.ENDC
					continue
			except Exception , e :
				print bcolors.OKBLUE + str(e) + bcolors.ENDC
			fetsl = {}
			maxs = cursor.fetchone()
			# format None to 0 on maxs and mins
			for i in fetlis :
				if maxs.has_key( i ) :
					if maxs[i] == None :
						maxs[i] = 0
				if mins.has_key( i ) :
					if mins[i] == None :
						mins[i] = 0
			mdf = maxs['uptime'] - mins['uptime']
			fetsl['server'] = srv[ 'ip' ]
			fetsl['tps'] = ( maxs['com_delete'] +  maxs['com_insert'] +  maxs['com_update'] - mins['com_delete'] +  mins['com_insert'] +  mins['com_update'] ) / mdf
			fetsl['qps'] = ( maxs['questions'] - mins['questions'] ) / mdf
			
			if maxs['innodb_data_read'] > 0 :
				fetsl['inr'] = ( maxs['innodb_data_read'] - mins['innodb_data_read'] )
			else :
				fetsl['inr'] = 0
				
			if maxs['innodb_data_written'] > 0 :
				fetsl['inw'] = ( maxs['innodb_data_written'] - mins['innodb_data_written'] )
			else :
				fetsl['inw'] = 0

			if maxs['innodb_os_log_written'] > 0 :
				fetsl['log'] = ( maxs['innodb_os_log_written'] - mins['innodb_os_log_written'] )
			else :
				fetsl['log'] = 0

			if maxs['innodb_dblwr_pages_written'] > 0 :
				fetsl['dbw'] = ( maxs['innodb_dblwr_pages_written'] - mins['innodb_dblwr_pages_written'] )
			else :
				fetsl['dbw'] = 0
			# w + redo log + double write / 16K = write io
			fetsl['inwio'] =  ( fetsl['inw'] + fetsl['log'] + fetsl['dbw'] ) / 16384 / 1000000
			# r / 16K = read io
			fetsl['inrio'] = fetsl['inr'] / 16384 / 1000000

			# innodb r + w = innodb io
			fetsl['inio'] = fetsl['inwio'] + fetsl['inrio']

			# innodb r mb = inrg
			fetsl['inrg'] = fetsl['inr'] / 1073741824

			# innodb w mb = inwg
			fetsl['inwg'] = ( fetsl['inw'] + fetsl['log'] + fetsl['dbw'] ) / 1073741824

			# innodb wr mb = inwrg
			fetsl['inwrg'] = fetsl['inrg'] + fetsl['inwg']

			if maxs['innodb_data_reads'] > 0 :
				fetsl['inrq'] = maxs['innodb_data_reads'] - mins['innodb_data_reads']
			else :
				fetsl['inrq'] = 0
			# print  maxs['innodb_data_writes'] , maxs['innodb_log_writes'] , maxs['innodb_dblwr_writes'] , mins['innodb_data_writes'] , mins['innodb_log_writes'] , mins['innodb_dblwr_writes']
			fetsl['inwq'] = maxs['innodb_data_writes'] + maxs['innodb_log_writes'] + maxs['innodb_dblwr_writes'] - mins['innodb_data_writes'] - mins['innodb_log_writes'] - mins['innodb_dblwr_writes']
			# innodb r request + w request = innodb rw reques == inreq
			fetsl['inreq'] = fetsl['inwq'] + fetsl['inrq']

			if maxs['key_reads'] > 0 :
				fetsl['keyrio'] = ( maxs['key_reads'] - mins['key_reads'] ) / 1000000
			else :
				fetsl['keyrio'] = 0
			if maxs['key_writes'] > 0 :
				fetsl['keywio'] = ( maxs['key_writes'] - mins['key_writes'] ) / 1000000
			else :
				fetsl['keywio'] = 0
			fetsl['keyio'] = fetsl['keyrio'] + fetsl['keywio']
			fetsl['wio'] = fetsl['inwio'] + fetsl['keywio']
			fetsl['rio'] = fetsl['inrio'] + fetsl['keyrio']
			fetsl['io'] = fetsl['inio'] + fetsl['keyio']
			fetsl['netr'] = ( maxs['bytes_received'] - mins['bytes_received'] ) / 1073741824
			fetsl['nets'] = ( maxs['bytes_sent'] - mins['bytes_sent'] ) / 1073741824
			fetsl['net'] = fetsl['netr'] + fetsl['nets']
			fetsl['days'] = date
			publisr.append( fetsl )
			# Write to Microsoft Excel File
			rows += 1
			column = 0
			for i in pubret :
				f.write(rows , column , fetsl[i] )
				column += 1
		for res in pubret :
			for rets in publisr :
				if len(rets) < 1 :
					continue
				if srv_ret.has_key( res ) :
					srv_ret[ res ] += rets[ res ]
				else :
					srv_ret[ res ] = rets[ res ]
		ret.append( srv_ret )
		days -= 1
	print bcolors.OKBLUE + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + ' Write to Sheet status_detail , status , space working book in %s' % fi + bcolors.ENDC
	f = wbk.add_sheet('status')
	column=0
	for i in pubret :
		if i == 'server' :
			continue
		f.write(0,column,i)
		column+=1
	rows = 1
	for res in ret :
		if len(res) < 1 :
			continue
		column = 0
		res['days']=res['days'][:4]
		for i in pubret :
			if i == 'server' :
				continue
			f.write(rows , column , res[i] )
			column+=1
		rows+=1
	print bcolors.OKBLUE + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + ' Write status for data space , datalen , idxlen , rows in %s sheet[dis]' % fi + bcolors.ENDC
	fn = wbk.add_sheet('space')
	cursor.execute( daidx.encode('utf-8') )
	ret = cursor.fetchall()
	diso = [ 'dates' , 'RowBillion' , 'DG' , 'IG' , 'CapG' , 'IncRow' , 'IncDatM' , 'IncIdxM' , 'IncM']
	
	column=0
	for i in diso :
		fn.write( 0 , column , i )
		column+=1
	rows = 1
	for res in ret :
		if len(res) < 1 :
			continue
		column = 0
		res['dates'] = str(res['dates']).replace('-' , '')[4:]
		for i in diso :
			fn.write( rows , column , res[i] )
			column+=1
		rows+=1
	wbk.save( fi )
	if cursor :
		cursor.close()
	if db :
		db.close()
	print bcolors.OKBLUE + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + ' Operation Sucessful , file ' + fi + bcolors.ENDC

def help() :
	print """
	Usage :
	%s [Options]
	-d	debug mode
	-o	write file as csv format
	-c	can't write file , display console
	-h display this
	-o and -c write to csv and display , csv can't write debug to csv
	"""
if __name__ == '__main__' :
	main()
